package com.shuohe.controller.develop.poi;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import com.shuohe.controller.develop.url.UrlController;
import com.shuohe.dao.develop.poi.UrlPoiDao;
import com.shuohe.dao.develop.poi.UrlPoiHeaderDao;
import com.shuohe.dao.develop.poi.UrlPoiIutputParaDao;
import com.shuohe.entity.develop.poi.UrlPoi;
import com.shuohe.entity.develop.poi.UrlPoiHeader;
import com.shuohe.entity.develop.poi.UrlPoiIutputPara;
import com.shuohe.service.develop.poi.UrlPoiService;
import com.shuohe.service.util.sql.SqlServiceImpl;
import com.shuohe.util.db.DbQuerySql;
import com.shuohe.util.json.Json;
import com.shuohe.util.poi.POI;
import com.shuohe.util.returnBean.ReturnBean;

@Controller  
@RequestMapping("/develop/url/poi/*")  
public class UrlPoiController {

	@Resource
	private UrlPoiService urlPoiService;
	
	@Autowired
	private UrlPoiDao urlPoiDao; 
	
	@Autowired
	private UrlPoiHeaderDao urlPoiHeaderDao; 
	@Autowired
	private UrlPoiIutputParaDao urlPoiIutputParaDao;

	@Resource
	private SqlServiceImpl sqlService;
	
	private Logger logger = LoggerFactory.getLogger(UrlPoiController.class);
	
	@RequestMapping(value="findUrlById.do")  
    public @ResponseBody UrlPoi findUrlById(HttpServletRequest request,HttpServletResponse response)
	{
		String _id=request.getParameter("id");
		try 
		{
			int id = Integer.parseInt(_id);
			return urlPoiService.findUrlById(id);
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
			return null;
		}		
	}
	@RequestMapping(value="findUrlSqlById.do",produces="text/html; charset=UTF-8")  
    public @ResponseBody String findUrlSqlById(HttpServletRequest request,HttpServletResponse response)
	{
		String _id=request.getParameter("id");		
		try 
		{
			int id = Integer.parseInt(_id);
			UrlPoi urlPoi =  urlPoiService.findUrlById(id);
			if(urlPoi!=null)
				return urlPoi.getSql_str();
			else
				return null;
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
			return null;
		}				
	}
	@RequestMapping(value="findUrlPoiHeaderByPid.do")  
    public @ResponseBody List<UrlPoiHeader> findUrlPoiHeaderByPid(HttpServletRequest request,HttpServletResponse response)
	{
		String _pid=request.getParameter("pid");
		try 
		{
			int pid = Integer.parseInt(_pid);
			return urlPoiService.findUrlPoiHeaderByPid(pid);
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
			return null;
		}	
	}
	
	@RequestMapping(value="findUrlPoiIutputParaByPid.do")  
    public @ResponseBody List<UrlPoiIutputPara> findUrlPoiIutputParaByPid(HttpServletRequest request,HttpServletResponse response){
		String _pid=request.getParameter("pid");
		try 
		{
			int pid = Integer.parseInt(_pid);
			return urlPoiService.findUrlPoiIutputParaByPid(pid);
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
			return null;
		}	
	}
	
	@RequestMapping(value="getByPid.do")  
    public @ResponseBody List<UrlPoi> getByPid(HttpServletRequest request,HttpServletResponse response){
		String _pid=request.getParameter("pid");
		try 
		{
			int pid = Integer.parseInt(_pid);
			return urlPoiService.getUrlPoiByPid(pid);
		}
		catch(NumberFormatException e)
		{
			e.printStackTrace();
			return null;
		}	
	}
	
	@RequestMapping(value="save.do")  
    public @ResponseBody ReturnBean save(HttpServletRequest request,HttpServletResponse response){
		Gson reGson = new Gson();
		String url_ = request.getParameter("url").replaceAll("\"\"", "null");
		String urlPoiHeader = request.getParameter("UrlPoiHeader").replaceAll("\"\"", "null");
		String urlPoiIutputPara = request.getParameter("UrlPoiIutputPara").replaceAll("\"\"", "null");
		
		UrlPoi url = reGson.fromJson(url_, new TypeToken<UrlPoi>(){}.getType());
		ArrayList<UrlPoiHeader> urlPoiHeader_list = reGson.fromJson(urlPoiHeader, new TypeToken<ArrayList<UrlPoiHeader>>(){}.getType());
		ArrayList<UrlPoiIutputPara> urlPoiIutputPara_list = reGson.fromJson(urlPoiIutputPara, new TypeToken<ArrayList<UrlPoiIutputPara>>(){}.getType());
		try
		{
			urlPoiService.save(url, urlPoiHeader_list,urlPoiIutputPara_list);
			return new ReturnBean(true,"");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return new ReturnBean(false,e.getMessage());
		}
	}
	@RequestMapping(value="update.do")  
    public @ResponseBody ReturnBean update(HttpServletRequest request,HttpServletResponse response){
		Gson reGson = new Gson();
		String url_ = request.getParameter("url").replaceAll("\"\"", "null");
		String urlPoiHeader = request.getParameter("UrlPoiHeader").replaceAll("\"\"", "null");
		String urlPoiIutputPara = request.getParameter("UrlPoiIutputPara").replaceAll("\"\"", "null");
		
		UrlPoi url = (UrlPoi) Json.toObject(UrlPoi.class, url_);		
		ArrayList<UrlPoiHeader> UrlIutputPara_list = reGson.fromJson(urlPoiHeader, new TypeToken<ArrayList<UrlPoiHeader>>(){}.getType());
		ArrayList<UrlPoiIutputPara> urlPoiIutputPara_list = reGson.fromJson(urlPoiIutputPara, new TypeToken<ArrayList<UrlPoiIutputPara>>(){}.getType());
		
		try
		{
			urlPoiService.update(url, UrlIutputPara_list,urlPoiIutputPara_list);
			return new ReturnBean(true,"");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return new ReturnBean(false,e.getMessage());
		}
	}
	@RequestMapping(value="delete.do")  
    public @ResponseBody ReturnBean delete(HttpServletRequest request,HttpServletResponse response){	
		String _id = request.getParameter("id").replaceAll("\"\"", "null");				
						
		try
		{
			int id = Integer.parseInt(_id);			
			UrlPoi urlPoi = urlPoiService.findUrlById(id);
			if(urlPoi == null)	return new ReturnBean(false,"ID没有对应的连接实例"); 
			urlPoiService.delete(urlPoi);
			return new ReturnBean(true,"删除成功");
		}		
		catch(NumberFormatException e)
		{
			e.printStackTrace();
			return new ReturnBean(false,"网络通讯失败");
		}
		catch(Exception e)
		{
			e.printStackTrace();
			return new ReturnBean(false,"删除失败");
		}
	}
	
	@RequestMapping(value="getExcelByName.do")  
    public @ResponseBody void getExcelByName(HttpServletRequest request,HttpServletResponse response) throws SQLException, IOException{	
		String name = request.getParameter("name");	
		UrlPoi url = urlPoiService.findByName(name);
		
		//获取并设置其他外联输入检索条件
		List<UrlPoiIutputPara> input_list= urlPoiIutputParaDao.findByPid(url.getId());		
		String sql = url.getSql_str();
		
		for(Object o:input_list)
		{
			UrlPoiIutputPara u = (UrlPoiIutputPara)o;
			String key = u.getSql_para_name();
			String value = request.getParameter(u.getName());
			String expr = u.getType();
			boolean is_necessary = u.isIs_necessary();
			logger.info("is_necessary = "+is_necessary);
			
			if("date>=".equals(expr))
			{
				if(is_necessary)
					sql += DbQuerySql.andDateAGreatThanBNecessary(key, value);
				else
					sql += DbQuerySql.andDateAGreatThanB(key, value);		
			}
			else if("date<=".equals(expr))
			{
				if(is_necessary)		
					sql += DbQuerySql.andDateALessrThanBNecessary(key, value);
				else		
					sql += DbQuerySql.andDateALessrThanB(key, value);
			}
			else if(">".equals(expr))
			{
				if(is_necessary)		
					sql += DbQuerySql.andAGreaterThanBNecessary(key, value);	
				else
					sql += DbQuerySql.andAGreaterThanB(key, value);	
			}
			else if("<".equals(expr))
			{
				if(is_necessary)
					sql += DbQuerySql.andALessThanBNecessary(key, value);
				else
					sql += DbQuerySql.andALessThanB(key, value);
			}
			else if("=".equals(expr))
			{
				if(is_necessary)
					sql += DbQuerySql.andAEqualToBNecessary(key, value);
				else
					sql += DbQuerySql.andAEqualToB(key, value);
			}
			else if("like".equals(expr))
			{
				sql += DbQuerySql.andALikeB(key, value);
			}
		}
				
		POI poi = new POI();
		poi.setFile_name(url.getFileName());
		poi.setTable("sheet1");
		
		//设置excel表头，及表头和数据库字段对应关系
		ArrayList<String> headers = new ArrayList<String>();
		ArrayList<String> keys = new ArrayList<String>();
		List<UrlPoiHeader> k_v_list= urlPoiHeaderDao.findByPid(url.getId());		
		for(Object o:k_v_list)
		{
			UrlPoiHeader u = (UrlPoiHeader)o;
			String key_str = u.getKey_str();
			String header_str = u.getHeader_str();
			keys.add(key_str);
			headers.add(header_str);
		}
	 	poi.setHeaders(headers);
	 	poi.setKeys(keys);

		//数据库检索及流输出
	 	List<Map<String, Object>> data;
	 	data = sqlService.getDataForList(sql);
		poi.setData(data);
		POI.Rb rb = poi.createExcel();
		String xfileName = URLEncoder.encode(url.getFileName(), "utf-8");
        response.setHeader("content-disposition", "attachment;filename=" + xfileName);
        OutputStream out = response.getOutputStream();
        rb.getExcel().write(out);
        rb.getExcel().close();
        out.close(); 
	}
	
	
	
}
